#include "place_db.h"
#include <sstream>

// 添加地点
bool PlaceDB::addPlace(const Place& place) {
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    std::string sql = "INSERT INTO places (name, type, latitude, longitude, outline, description, address) "
                      "VALUES (?, ?, ?, ?, ?, ?, ?);";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    // 绑定参数
    sqlite3_bind_text(stmt, 1, place.name.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, place.type.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(stmt, 3, place.latitude);
    sqlite3_bind_double(stmt, 4, place.longitude);
    sqlite3_bind_text(stmt, 5, place.outline.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 6, place.description.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 7, place.address.c_str(), -1, SQLITE_TRANSIENT);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    return rc == SQLITE_DONE;
}

// 获取所有地点
std::vector<Place> PlaceDB::getAllPlaces() {
    std::vector<Place> places;
    sqlite3* conn = db.getConnection();
    if (!conn) return places;
    
    std::string sql = "SELECT id, name, type, latitude, longitude, outline, description, address, popularity, rating FROM places;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return places;
    }
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Place place;
        place.id = sqlite3_column_int(stmt, 0);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.latitude = sqlite3_column_double(stmt, 3);
        place.longitude = sqlite3_column_double(stmt, 4);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 5))
            place.outline = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
        if (sqlite3_column_text(stmt, 7))
            place.address = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
        
        // 添加热度和评价字段
        place.popularity = sqlite3_column_double(stmt, 8);
        place.rating = sqlite3_column_double(stmt, 9);
        
        places.push_back(place);
    }
    
    sqlite3_finalize(stmt);
    return places;
}

// 按类型获取地点
std::vector<Place> PlaceDB::getPlacesByType(const std::string& type) {
    std::vector<Place> places;
    sqlite3* conn = db.getConnection();
    if (!conn) return places;
    
    std::string sql = "SELECT id, name, type, latitude, longitude, outline, description, address "
                     "FROM places WHERE type = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return places;
    }
    
    sqlite3_bind_text(stmt, 1, type.c_str(), -1, SQLITE_TRANSIENT);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Place place;
        place.id = sqlite3_column_int(stmt, 0);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.latitude = sqlite3_column_double(stmt, 3);
        place.longitude = sqlite3_column_double(stmt, 4);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 5))
            place.outline = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
        if (sqlite3_column_text(stmt, 7))
            place.address = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
        
        places.push_back(place);
    }
    
    sqlite3_finalize(stmt);
    return places;
}

// 获取单个地点
Place PlaceDB::getPlaceById(int id) {
    Place place;
    sqlite3* conn = db.getConnection();
    if (!conn) return place;
    
    std::string sql = "SELECT id, name, type, latitude, longitude, outline, description, address, popularity, rating "
                     "FROM places WHERE id = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return place;
    }
    
    sqlite3_bind_int(stmt, 1, id);
    
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        place.id = sqlite3_column_int(stmt, 0);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.latitude = sqlite3_column_double(stmt, 3);
        place.longitude = sqlite3_column_double(stmt, 4);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 5))
            place.outline = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
        if (sqlite3_column_text(stmt, 7))
            place.address = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
            
        // 添加热度和评价字段
        place.popularity = sqlite3_column_double(stmt, 8);
        place.rating = sqlite3_column_double(stmt, 9);
    }
    
    sqlite3_finalize(stmt);
    return place;
}

// 搜索地点
std::vector<Place> PlaceDB::searchPlaces(const std::string& keyword) {
    std::vector<Place> places;
    sqlite3* conn = db.getConnection();
    if (!conn) return places;
    
    std::string sql = "SELECT id, name, type, latitude, longitude, outline, description, address, popularity, rating "
                     "FROM places WHERE name LIKE ? OR description LIKE ? OR address LIKE ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return places;
    }
    
    std::string pattern = "%" + keyword + "%";
    sqlite3_bind_text(stmt, 1, pattern.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, pattern.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, pattern.c_str(), -1, SQLITE_TRANSIENT);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Place place;
        place.id = sqlite3_column_int(stmt, 0);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.latitude = sqlite3_column_double(stmt, 3);
        place.longitude = sqlite3_column_double(stmt, 4);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 5))
            place.outline = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
        if (sqlite3_column_text(stmt, 7))
            place.address = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
        
        // 添加热度和评价字段
        place.popularity = sqlite3_column_double(stmt, 8);
        place.rating = sqlite3_column_double(stmt, 9);
        
        places.push_back(place);
    }
    
    sqlite3_finalize(stmt);
    return places;
}

// 更新地点
bool PlaceDB::updatePlace(const Place& place) {
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    std::string sql = "UPDATE places SET name = ?, type = ?, latitude = ?, longitude = ?, "
                      "outline = ?, description = ?, address = ?, popularity = ?, rating = ? WHERE id = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    sqlite3_bind_text(stmt, 1, place.name.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, place.type.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(stmt, 3, place.latitude);
    sqlite3_bind_double(stmt, 4, place.longitude);
    sqlite3_bind_text(stmt, 5, place.outline.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 6, place.description.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 7, place.address.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(stmt, 8, place.popularity);
    sqlite3_bind_double(stmt, 9, place.rating);
    sqlite3_bind_int(stmt, 10, place.id);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    return rc == SQLITE_DONE;
}

// 删除地点
bool PlaceDB::deletePlace(int id) {
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    // 首先删除关联的内部地点
    std::string deleteSql = "DELETE FROM internal_places WHERE parent_id = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, deleteSql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    sqlite3_bind_int(stmt, 1, id);
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    // 然后删除地点
    deleteSql = "DELETE FROM places WHERE id = ?;";
    
    rc = sqlite3_prepare_v2(conn, deleteSql.c_str(), -1, &stmt, nullptr);
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    sqlite3_bind_int(stmt, 1, id);
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    return rc == SQLITE_DONE;
}

// 添加内部地点
bool PlaceDB::addInternalPlace(const InternalPlace& place) {
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    std::string sql = "INSERT INTO internal_places (parent_id, name, type, latitude, longitude, description) "
                      "VALUES (?, ?, ?, ?, ?, ?);";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    sqlite3_bind_int(stmt, 1, place.parent_id);
    sqlite3_bind_text(stmt, 2, place.name.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, place.type.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(stmt, 4, place.latitude);
    sqlite3_bind_double(stmt, 5, place.longitude);
    sqlite3_bind_text(stmt, 6, place.description.c_str(), -1, SQLITE_TRANSIENT);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    return rc == SQLITE_DONE;
}

// 获取地点内部地点
std::vector<InternalPlace> PlaceDB::getInternalPlaces(int parentId) {
    std::vector<InternalPlace> places;
    sqlite3* conn = db.getConnection();
    if (!conn) return places;
    
    std::string sql = "SELECT id, parent_id, name, type, latitude, longitude, description "
                     "FROM internal_places WHERE parent_id = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return places;
    }
    
    sqlite3_bind_int(stmt, 1, parentId);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        InternalPlace place;
        place.id = sqlite3_column_int(stmt, 0);
        place.parent_id = sqlite3_column_int(stmt, 1);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
        place.latitude = sqlite3_column_double(stmt, 4);
        place.longitude = sqlite3_column_double(stmt, 5);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
        
        places.push_back(place);
    }
    
    sqlite3_finalize(stmt);
    return places;
}

// 获取所有缺少轮廓数据的地点
std::vector<Place> PlaceDB::getAllPlacesWithoutOutline() {
    std::vector<Place> places;
    sqlite3* conn = db.getConnection();
    if (!conn) return places;
    
    std::string sql = "SELECT id, name, type, latitude, longitude, outline, description, address "
                     "FROM places WHERE outline IS NULL OR outline = '';";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return places;
    }
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Place place;
        place.id = sqlite3_column_int(stmt, 0);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.latitude = sqlite3_column_double(stmt, 3);
        place.longitude = sqlite3_column_double(stmt, 4);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 5))
            place.outline = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
        if (sqlite3_column_text(stmt, 7))
            place.address = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
        
        places.push_back(place);
    }
    
    sqlite3_finalize(stmt);
    return places;
}

// 更新地点轮廓数据
bool PlaceDB::updatePlaceOutline(int placeId, const std::string& outlineData) {
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    std::string sql = "UPDATE places SET outline = ? WHERE id = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    sqlite3_bind_text(stmt, 1, outlineData.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 2, placeId);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    return rc == SQLITE_DONE;
}

// 批量添加地点
bool PlaceDB::addPlacesBatch(const std::vector<Place>& places) {
    if (places.empty()) return true;  // 空列表视为成功
    
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    // 开始事务
    char* errMsg = nullptr;
    if (sqlite3_exec(conn, "BEGIN TRANSACTION", nullptr, nullptr, &errMsg) != SQLITE_OK) {
        std::cerr << "开始事务失败: " << errMsg << std::endl;
        sqlite3_free(errMsg);
        return false;
    }
    
    std::string sql = "INSERT INTO places (name, type, latitude, longitude, outline, description, address) "
                      "VALUES (?, ?, ?, ?, ?, ?, ?);";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        sqlite3_exec(conn, "ROLLBACK", nullptr, nullptr, &errMsg);
        return false;
    }
    
    bool success = true;
    
    for (const auto& place : places) {
        // 重置语句以便重用
        sqlite3_reset(stmt);
        sqlite3_clear_bindings(stmt);
        
        // 绑定参数
        sqlite3_bind_text(stmt, 1, place.name.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 2, place.type.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_double(stmt, 3, place.latitude);
        sqlite3_bind_double(stmt, 4, place.longitude);
        sqlite3_bind_text(stmt, 5, place.outline.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 6, place.description.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 7, place.address.c_str(), -1, SQLITE_TRANSIENT);
        
        rc = sqlite3_step(stmt);
        
        if (rc != SQLITE_DONE) {
            std::cerr << "批量添加地点失败: " << sqlite3_errmsg(conn) << std::endl;
            success = false;
            break;
        }
    }
    
    sqlite3_finalize(stmt);
    
    // 根据操作结果提交或回滚事务
    if (success) {
        if (sqlite3_exec(conn, "COMMIT", nullptr, nullptr, &errMsg) != SQLITE_OK) {
            std::cerr << "提交事务失败: " << errMsg << std::endl;
            sqlite3_free(errMsg);
            success = false;
        }
    } else {
        if (sqlite3_exec(conn, "ROLLBACK", nullptr, nullptr, &errMsg) != SQLITE_OK) {
            std::cerr << "回滚事务失败: " << errMsg << std::endl;
            sqlite3_free(errMsg);
        }
    }
    
    return success;
}

// 获取单个内部地点
InternalPlace PlaceDB::getInternalPlaceById(int id) {
    InternalPlace place;
    sqlite3* conn = db.getConnection();
    if (!conn) return place;
    
    std::string sql = "SELECT id, parent_id, name, type, latitude, longitude, description "
                     "FROM internal_places WHERE id = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return place;
    }
    
    sqlite3_bind_int(stmt, 1, id);
    
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        place.id = sqlite3_column_int(stmt, 0);
        place.parent_id = sqlite3_column_int(stmt, 1);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
        place.latitude = sqlite3_column_double(stmt, 4);
        place.longitude = sqlite3_column_double(stmt, 5);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
    }
    
    sqlite3_finalize(stmt);
    return place;
}

// 更新内部地点
bool PlaceDB::updateInternalPlace(const InternalPlace& place) {
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    std::string sql = "UPDATE internal_places SET parent_id = ?, name = ?, type = ?, "
                      "latitude = ?, longitude = ?, description = ? WHERE id = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    sqlite3_bind_int(stmt, 1, place.parent_id);
    sqlite3_bind_text(stmt, 2, place.name.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, place.type.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(stmt, 4, place.latitude);
    sqlite3_bind_double(stmt, 5, place.longitude);
    sqlite3_bind_text(stmt, 6, place.description.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 7, place.id);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    return rc == SQLITE_DONE;
}

// 删除内部地点
bool PlaceDB::deleteInternalPlace(int id) {
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    std::string sql = "DELETE FROM internal_places WHERE id = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    sqlite3_bind_int(stmt, 1, id);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    return rc == SQLITE_DONE;
}

// 获取所有道路
std::vector<Road> PlaceDB::getAllRoads() {
    std::vector<Road> roads;
    sqlite3* conn = db.getConnection();
    if (!conn) return roads;
    
    std::string sql = "SELECT id, start_lat, start_lng, end_lat, end_lng, road_type FROM roads;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return roads;
    }
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Road road;
        road.id = sqlite3_column_int(stmt, 0);
        road.start_lat = sqlite3_column_double(stmt, 1);
        road.start_lng = sqlite3_column_double(stmt, 2);
        road.end_lat = sqlite3_column_double(stmt, 3);
        road.end_lng = sqlite3_column_double(stmt, 4);
        
        if (sqlite3_column_text(stmt, 5))
            road.road_type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        
        roads.push_back(road);
    }
    
    sqlite3_finalize(stmt);
    return roads;
}

// 添加道路
bool PlaceDB::addRoad(const Road& road) {
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    std::string sql = "INSERT INTO roads (start_lat, start_lng, end_lat, end_lng, road_type) "
                      "VALUES (?, ?, ?, ?, ?);";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    sqlite3_bind_double(stmt, 1, road.start_lat);
    sqlite3_bind_double(stmt, 2, road.start_lng);
    sqlite3_bind_double(stmt, 3, road.end_lat);
    sqlite3_bind_double(stmt, 4, road.end_lng);
    sqlite3_bind_text(stmt, 5, road.road_type.c_str(), -1, SQLITE_TRANSIENT);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    return rc == SQLITE_DONE;
}

// 获取两点间道路
std::vector<Road> PlaceDB::getRoadsBetweenPoints(double lat1, double lng1, double lat2, double lng2, double radius) {
    std::vector<Road> roads;
    sqlite3* conn = db.getConnection();
    if (!conn) return roads;
    
    // 简单的空间查询，可以根据需要优化
    std::string sql = "SELECT id, start_lat, start_lng, end_lat, end_lng, road_type FROM roads "
                     "WHERE ((start_lat BETWEEN ? AND ?) OR (end_lat BETWEEN ? AND ?)) "
                     "AND ((start_lng BETWEEN ? AND ?) OR (end_lng BETWEEN ? AND ?));";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return roads;
    }
    
    double minLat = std::min(lat1, lat2) - radius;
    double maxLat = std::max(lat1, lat2) + radius;
    double minLng = std::min(lng1, lng2) - radius;
    double maxLng = std::max(lng1, lng2) + radius;
    
    sqlite3_bind_double(stmt, 1, minLat);
    sqlite3_bind_double(stmt, 2, maxLat);
    sqlite3_bind_double(stmt, 3, minLat);
    sqlite3_bind_double(stmt, 4, maxLat);
    sqlite3_bind_double(stmt, 5, minLng);
    sqlite3_bind_double(stmt, 6, maxLng);
    sqlite3_bind_double(stmt, 7, minLng);
    sqlite3_bind_double(stmt, 8, maxLng);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Road road;
        road.id = sqlite3_column_int(stmt, 0);
        road.start_lat = sqlite3_column_double(stmt, 1);
        road.start_lng = sqlite3_column_double(stmt, 2);
        road.end_lat = sqlite3_column_double(stmt, 3);
        road.end_lng = sqlite3_column_double(stmt, 4);
        
        if (sqlite3_column_text(stmt, 5))
            road.road_type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        
        roads.push_back(road);
    }
    
    sqlite3_finalize(stmt);
    return roads;
}

// 搜索地点并按热度排序
std::vector<Place> PlaceDB::searchPlacesByPopularity(const std::string& keyword) {
    std::vector<Place> places;
    sqlite3* conn = db.getConnection();
    if (!conn) return places;
    
    std::string sql = "SELECT id, name, type, latitude, longitude, outline, description, address, popularity, rating "
                     "FROM places WHERE (name LIKE ? OR description LIKE ? OR address LIKE ?) "
                     "ORDER BY popularity DESC;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return places;
    }
    
    std::string pattern = "%" + keyword + "%";
    sqlite3_bind_text(stmt, 1, pattern.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, pattern.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, pattern.c_str(), -1, SQLITE_TRANSIENT);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Place place;
        place.id = sqlite3_column_int(stmt, 0);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.latitude = sqlite3_column_double(stmt, 3);
        place.longitude = sqlite3_column_double(stmt, 4);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 5))
            place.outline = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
        if (sqlite3_column_text(stmt, 7))
            place.address = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
        
        place.popularity = sqlite3_column_double(stmt, 8);
        place.rating = sqlite3_column_double(stmt, 9);
        
        places.push_back(place);
    }
    
    sqlite3_finalize(stmt);
    return places;
}

// 搜索地点并按评价排序
std::vector<Place> PlaceDB::searchPlacesByRating(const std::string& keyword) {
    std::vector<Place> places;
    sqlite3* conn = db.getConnection();
    if (!conn) return places;
    
    std::string sql = "SELECT id, name, type, latitude, longitude, outline, description, address, popularity, rating "
                     "FROM places WHERE (name LIKE ? OR description LIKE ? OR address LIKE ?) "
                     "ORDER BY rating DESC;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return places;
    }
    
    std::string pattern = "%" + keyword + "%";
    sqlite3_bind_text(stmt, 1, pattern.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, pattern.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, pattern.c_str(), -1, SQLITE_TRANSIENT);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Place place;
        place.id = sqlite3_column_int(stmt, 0);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.latitude = sqlite3_column_double(stmt, 3);
        place.longitude = sqlite3_column_double(stmt, 4);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 5))
            place.outline = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
        if (sqlite3_column_text(stmt, 7))
            place.address = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
        
        place.popularity = sqlite3_column_double(stmt, 8);
        place.rating = sqlite3_column_double(stmt, 9);
        
        places.push_back(place);
    }
    
    sqlite3_finalize(stmt);
    return places;
}

// 获取推荐地点（景点和学校）按热度排序
std::vector<Place> PlaceDB::getRecommendedPlacesByPopularity(int limit) {
    std::vector<Place> places;
    sqlite3* conn = db.getConnection();
    if (!conn) return places;
    
    std::string sql = "SELECT id, name, type, latitude, longitude, outline, description, address, popularity, rating "
                     "FROM places WHERE type IN ('景点', '学校') "
                     "ORDER BY popularity DESC LIMIT ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return places;
    }
    
    sqlite3_bind_int(stmt, 1, limit);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Place place;
        place.id = sqlite3_column_int(stmt, 0);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.latitude = sqlite3_column_double(stmt, 3);
        place.longitude = sqlite3_column_double(stmt, 4);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 5))
            place.outline = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
        if (sqlite3_column_text(stmt, 7))
            place.address = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
        
        place.popularity = sqlite3_column_double(stmt, 8);
        place.rating = sqlite3_column_double(stmt, 9);
        
        places.push_back(place);
    }
    
    sqlite3_finalize(stmt);
    return places;
}

// 获取推荐地点（景点和学校）按评价排序
std::vector<Place> PlaceDB::getRecommendedPlacesByRating(int limit) {
    std::vector<Place> places;
    sqlite3* conn = db.getConnection();
    if (!conn) return places;
    
    std::string sql = "SELECT id, name, type, latitude, longitude, outline, description, address, popularity, rating "
                     "FROM places WHERE type IN ('景点', '学校') "
                     "ORDER BY rating DESC LIMIT ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return places;
    }
    
    sqlite3_bind_int(stmt, 1, limit);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Place place;
        place.id = sqlite3_column_int(stmt, 0);
        place.name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
        place.type = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
        place.latitude = sqlite3_column_double(stmt, 3);
        place.longitude = sqlite3_column_double(stmt, 4);
        
        // 处理可能为NULL的字段
        if (sqlite3_column_text(stmt, 5))
            place.outline = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
        if (sqlite3_column_text(stmt, 6))
            place.description = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 6));
        if (sqlite3_column_text(stmt, 7))
            place.address = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 7));
        
        place.popularity = sqlite3_column_double(stmt, 8);
        place.rating = sqlite3_column_double(stmt, 9);
        
        places.push_back(place);
    }
    
    sqlite3_finalize(stmt);
    return places;
}

// 更新地点热度
bool PlaceDB::updatePlacePopularity(int placeId, double newPopularity) {
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    std::cout << "DB: Update popularity for place " << placeId << " to " << newPopularity << std::endl;
    
    std::string sql = "UPDATE places SET popularity = ? WHERE id = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "SQL Error: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    sqlite3_bind_double(stmt, 1, newPopularity);
    sqlite3_bind_int(stmt, 2, placeId);
    
    rc = sqlite3_step(stmt);
    
    if (rc == SQLITE_DONE) {
        // 使用SQL查询获取修改的行数，而不是使用sqlite3_changes函数
        int changedRows = 0;
        std::string countSql = "SELECT changes()";
        sqlite3_stmt* countStmt = nullptr;
        if (sqlite3_prepare_v2(conn, countSql.c_str(), -1, &countStmt, nullptr) == SQLITE_OK) {
            if (sqlite3_step(countStmt) == SQLITE_ROW) {
                changedRows = sqlite3_column_int(countStmt, 0);
            }
            sqlite3_finalize(countStmt);
        }
        
        if (changedRows == 0) {
            std::cerr << "Warning: No records modified in DB" << std::endl;
        }
    } else {
        std::cerr << "Update failed: " << sqlite3_errmsg(conn) << std::endl;
    }
    
    sqlite3_finalize(stmt);
    
    return rc == SQLITE_DONE;
}

// 更新地点评价
bool PlaceDB::updatePlaceRating(int placeId, double newRating) {
    sqlite3* conn = db.getConnection();
    if (!conn) return false;
    
    std::string sql = "UPDATE places SET rating = ? WHERE id = ?;";
    
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(conn, sql.c_str(), -1, &stmt, nullptr);
    
    if (rc != SQLITE_OK) {
        std::cerr << "准备SQL语句失败: " << sqlite3_errmsg(conn) << std::endl;
        return false;
    }
    
    sqlite3_bind_double(stmt, 1, newRating);
    sqlite3_bind_int(stmt, 2, placeId);
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    return rc == SQLITE_DONE;
}

// 实现其他方法...
// 为了简洁，这里省略了剩余方法的实现 